/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import connect.ConnectDB;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author gm_ba_000
 */
public class OrgDAO {
    Connection conn;
    ResultSet rsJob = null;
    Statement st;
    PreparedStatement pst;
    ConnectDB cdb;

    public OrgDAO() {
        cdb = new ConnectDB();
        cdb.getConnect();
        this.conn = cdb.conn;
    }

    public Vector loadListDep() {
        try {
            String query = "SELECT * FROM Departments";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            Vector v = new Vector();
            
            while (rs.next()) {
                v.add(rs.getString("DEPTNO"));
            }
            
            return v;
        } catch (Exception e) {
            System.out.println("Error in loadListDep: " + e.getMessage());
            return null;
        }
    }
    
    public DefaultTableModel loadListEmp(int depNo){
        try {
            Vector colName = new Vector();
            colName.add("Employee ID");
            colName.add("Employee Name");
            colName.add("Section ID");
            colName.add("Designation ID");
            
            DefaultTableModel dtm = new DefaultTableModel(colName, 0);
            
            String query = "SELECT * FROM Employees WHERE DEPTNO = ?";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setInt(1, depNo);
            ResultSet rs = pst.executeQuery();
            
            while (rs.next()) {
                Vector rowData = new Vector();
                rowData.add(rs.getString("EMPID"));
                rowData.add(rs.getString("EMPNAME"));
                rowData.add(rs.getString("SECTID"));
                rowData.add(rs.getString("DESIGNID"));
                dtm.addRow(rowData);
            }
            
            return dtm;
        } catch (Exception e) {
            System.out.println("Error in loadListEmp(): " + e.getMessage());
            return null;
        }
    }
    
    public DefaultTableModel loadListSec(int depNo){
        try {
            Vector colName = new Vector();
            colName.add("Section ID");
            colName.add("Section Name");
            colName.add("Section Incharge");
            
            DefaultTableModel dtm = new DefaultTableModel(colName, 0);
            
            String query = "SELECT * FROM Sections WHERE DEPTNO = ?";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setInt(1, depNo);
            ResultSet rs = pst.executeQuery();
            
            while (rs.next()) {
                Vector rowData = new Vector();
                rowData.add(rs.getString("SECTID"));
                rowData.add(rs.getString("SECTNAME"));
                rowData.add(rs.getString("SECTINCH"));
                dtm.addRow(rowData);
            }
            
            return dtm;
        } catch (Exception e) {
            System.out.println("Error in loadListSec(): " + e.getMessage());
            return null;
        }
    }
    
    public DefaultTableModel loadListVac(int depNo){
        try {
            Vector colName = new Vector();
            colName.add("Vacancy ID");
            colName.add("Section ID");
            colName.add("Designation ID");
            colName.add("Date");
            colName.add("Status");
            
            DefaultTableModel dtm = new DefaultTableModel(colName, 0);
            
            String query = "SELECT * FROM Vacancies WHERE DEPTNO = ?";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setInt(1, depNo);
            ResultSet rs = pst.executeQuery();
            
            while (rs.next()) {
                Vector rowData = new Vector();
                rowData.add(rs.getString("VACANCYID"));
                rowData.add(rs.getString("SECTID"));
                rowData.add(rs.getString("DESIGNID"));
                rowData.add(rs.getString("VACANCYDATE"));
                rowData.add(rs.getString("STATUS"));
                dtm.addRow(rowData);
            }
            
            return dtm;
        } catch (Exception e) {
            System.out.println("Error in loadListSec(): " + e.getMessage());
            return null;
        }
    }
    
    public void closeConnect() {
        try {
            if (rsJob != null) {
                rsJob.close();
            }
            
            if (pst != null){
                pst.close();
            }
            
            if (st!= null) {
                st.close();
            }
            
            if (conn!=null){
                conn.close();
            }
            
            if (cdb.conn!=null) {
                cdb.conn.close();
            }
            System.out.println("Close connect: Organization");
        } catch (Exception e) {
            System.out.println("Error in close connection: " + e.getMessage());
        }
    }
}
